package com.culturalCenter.dataCenter.mapper.site;

import com.culturalCenter.dataCenter.customEntity.site.CustomSiteUsedCount;
import com.culturalCenter.dataCenter.customEntity.site.CustomSiteUsedHistory;
import com.culturalCenter.dataCenter.entity.site.SiteUsedCount;
import com.culturalCenter.dataCenter.mapper.base.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SiteUsedCountMapper extends BaseMapper<SiteUsedCount> {

    /**
     * 根据片区年月查场地使用率
     *
     * @param area
     * @param year
     * @param month
     *
     * @return
     */
    List<CustomSiteUsedCount> queryByDate(@Param("area") String area, @Param("year") String year, @Param("month") String month);

    /**
     * 根据年月查热门场地
     *
     * @param year
     * @param month
     *
     * @return
     */
//    @Select("SELECT (total-unusedDays)/total usedRate, siteName,(bookingNum+usedNum) AS bookingNum FROM cdc_site_used_count " +
//            "INNER JOIN cdc_site ON (siteCode = code) " +
//            "WHERE year = #{year} AND month = #{month} ORDER BY usedRate DESC, bookingNum DESC LIMIT 6")
    List<CustomSiteUsedCount> queryHotSiteListByDate(@Param("year") String year, @Param("month") String month);

    /**
     * 根据年月查闲置场地
     *
     * @param year
     * @param month
     *
     * @return
     */
//    @Select("SELECT (total-unusedDays)/total usedRate, siteName, unusedDays FROM cdc_site_used_count " +
//            "INNER JOIN cdc_site ON (siteCode = code) " +
//            "WHERE year = #{year} AND month = #{month} ORDER BY usedRate, unusedDays DESC")
    List<CustomSiteUsedCount> queryUnusedSiteByDate(@Param("year") String year, @Param("month") String month);

    /**
     * 批量插入
     *
     * @param siteUsedCountList
     */
    void insertBatch(List<SiteUsedCount> siteUsedCountList);

    /**
     * 删除近七天的统计
     */
    @Delete("DELETE FROM cdc_site_used_count WHERE `month` = -1")
    void deleteRecent();

    /**
     * 场地基本信息_本月统计数据
     *
     * @param siteCode
     *
     * @return
     */
//    @Select("SELECT bookingNum, serveNum, serveHours, insiderNum, outsiderNum, total,unusedDays FROM cdc_site_used_count " +
//            "WHERE year = DATE_FORMAT( NOW(), '%Y' ) AND month=DATE_FORMAT( NOW( ), '%m' ) AND siteCode = #{siteCode}")
    CustomSiteUsedCount selectSiteInfoCount(@Param("siteCode") String siteCode);

    /**
     * 历史使用率_查符合条件的场地
     * @param siteName
     * @return
     */
    List<CustomSiteUsedHistory> getUsageRateHistory(@Param("siteName") String siteName);

    /**
     * 根据siteId、起止时间获取场地历史使用率
     *
     * @param siteId
     * @param startDate
     * @param endDate
     *
     * @return
     */
    Double getUsedRate(@Param("siteId") String siteId, @Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 查全部场地使用率
     */
//    @Select("SELECT siteName,code siteCode,bookingNum / total usedRate FROM cdc_site_used_count " +
//            "INNER JOIN cdc_site ON siteId = refId " +
//            "WHERE YEAR = #{year} AND month = #{month}")
//    List<CustomSiteUsedCount> queryAllByDate(String year, String month);

}
